import os

from PIL import Image

import sqlutils.dbhelper as db
import websites.crmebs.api as crmebApi
from utils.excel.excel_util import excel_util
from utils.file_util import createContinDir
from utils.logger import loger
import shutil


class db_util():
    fileName = r'image.png'  # 图片名称
    webDB = "crmeb"  # 网站数据库

    def insertGoodsInfo(self, goodsType, standard, xlspath, baseImgPath, debug=False, sheet=1):
        """
        开始数据写入操作
        """
        api = crmebApi.api()
        excel = excel_util()

        loginStatus = api.crmebLogin()
        if loginStatus == "成功":
            rsheet, wb, ws = excel.openExcel(xlspath, sheet)
            if rsheet is not None and wb is not None and ws is not None:
                index = 0
                print("打开数据文件成功")
                for rows in rsheet.get_rows():
                    if api.addGoodsInfo(rows, index, standard, goodsType, baseImgPath, debug) is False:
                        loger.error("第" + str(index) + "条数据插入失败，Name=" + str(rows[0].value))
                    else:
                        if index != 0:
                            print("第" + str(index) + "条数据插入成功")
                    index = index + 1
                print("数据写入完成！共计写入" + str(index) + "条")
            else:
                print("xls文件操作异常")
                return None
        else:
            print(loginStatus)
            return False

    def removeGoodsImg(self, xlspath, dirStart, sheet=1, excelImgPath=""):
        """
        移动商品大图到待上传文件夹
        """

        excel = excel_util()
        rsheet, wb, ws = excel.openExcel(xlspath, sheet)
        if rsheet is not None and wb is not None and ws is not None:
            print("打开数据文件成功")
            allRows = int(rsheet.nrows - 1 + int(dirStart))
            dirPath = createContinDir(dirStart, allRows)

            imgIndex = 0  # 大图所在列
            index = 0
            toStartPath = int(dirStart)  # 目标图片目录的起始目录
            for rows in rsheet.get_rows():
                childIndex = 0
                for item in rows:
                    if index == 0 and item.value == "大图":  # 存储首列大图下标
                        imgIndex = childIndex
                    elif index != 0:
                        if childIndex == imgIndex:  # 到指定下标时，移动图片操作
                            fromPath = excelImgPath + r'\image' + str(int(item.value)) + '.png'

                            if os.path.exists(fromPath) is False:
                                fromPath = excelImgPath + r'\image' + str(int(item.value)) + '.jpeg'

                            toPath = dirPath + "\\" + str(toStartPath) + "\\" + self.fileName
                            if os.path.exists(fromPath):
                                shutil.copyfile(fromPath, toPath)
                            else:
                                print("文件夹" + str(toStartPath) + "未写入文件" + str(int(item.value)) + ".png")
                            toStartPath = toStartPath + 1
                    childIndex = childIndex + 1
                index = index + 1
        else:
            print("xls文件操作异常")

    def deleteGoods(self, idArr):
        """
        删除某商品信息
        idArr : 数组
        """
        if db.dbconnect(dbName=self.webDB):
            for goodsId in idArr:
                productSql = "DELETE FROM eb_store_product WHERE id=" + str(goodsId) + ";"
                attrSql = "DELETE FROM eb_store_product_attr WHERE product_id=" + str(goodsId) + ";"
                attrResultSql = "DELETE FROM eb_store_product_attr_result WHERE product_id=" + str(goodsId) + ";"
                cateSql = "DELETE FROM eb_store_product_cate WHERE product_id=" + str(goodsId) + ";"
                couponSql = "DELETE FROM eb_store_product_coupon WHERE product_id=" + str(goodsId) + ";"
                descriptionSql = "DELETE FROM eb_store_product_description WHERE product_id=" + str(goodsId) + ";"
                attrValueSql = "DELETE FROM eb_store_product_attr_value WHERE product_id=" + str(goodsId) + ";"

                db.executeSql(productSql)
                db.executeSql(attrValueSql)
                db.executeSql(attrSql)
                db.executeSql(attrResultSql)
                db.executeSql(cateSql)
                db.executeSql(couponSql)
                db.executeSql(descriptionSql)

    def rebuildDB(self, tableName=None):
        """
        删除数据表数据并且重建索引
        tableName: ["table_a","table_b"]
        """
        if db.dbconnect(dbName=self.webDB):
            if tableName is None:
                db.rebuildIndex("eb_store_product")
                db.rebuildIndex("eb_store_product_attr")
                db.rebuildIndex("eb_store_product_attr_result")
                db.rebuildIndex("eb_store_product_cate")
                db.rebuildIndex("eb_store_product_coupon")
                db.rebuildIndex("eb_store_product_description")
                db.rebuildIndex("eb_store_product_attr_value")
            else:
                for item in tableName:
                    db.rebuildIndex(item)

    def copyImg(self):
        """
        拷贝文件到新目录
        """
        imagePath = r'C:\Users\da\Desktop\123'
        fileList = os.listdir(imagePath)
        for item in fileList:
            num = str(int(item.split(".")[0]) + 6)
            newFile = r'C:\Users\da\Desktop\dir' + "\\" + num + "\detail.png"
            shutil.copyfile(imagePath + "\\" + item, newFile)


if __name__ == '__main__':
    util = db_util()
    util.copyImg()
